﻿using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ADOX;

namespace AnalysisSystem
{
    class OleDbMethod
    {
        #region 变量
        //分别存储模板页、按井号排序数据页、按日期排序数据页
        public static DataTable[] dataTables = new DataTable[4];
        private static string[] sheetNameSetString = { "1-静态", "2-1 日产", "production"};        
        #endregion
        #region 公有方法
        /// <summary>
        /// 获取文件路径
        /// </summary>
        /// <returns></returns>
        public static string GetPath()
        {
            try
            {
                OpenFileDialog fileOpenResult = new OpenFileDialog();
                if (fileOpenResult.ShowDialog() == DialogResult.OK)
                {
                    return fileOpenResult.FileName;
                }
                else
                {
                    return null;
                }
            }
            catch (FileNotFoundException e)
            {
                throw new Exception(e.Message);
            }
        }
        /// <summary>
        /// 读入Access文件的初始化函数，结果储存在dataTables变量中
        /// </summary>
        /// <param name="pathName">文件路径</param>
        public static void AccessReadInit(string pathName)
        {
            if (InitialCheck(pathName, true))
            {
                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathName);
                conn.Open();
                for (int i = 0; i < sheetNameSetString.Length; i++)
                {
                    fillTable(sheetNameSetString[i], i, conn);
                }
                TableDataFormat();
                GetDateOrderTable();
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 读入Excel文件的初始化函数，结果储存在dataTables变量中
        /// </summary>
        /// <param name="pathName">文件路径</param>
        public static void ExcelReadInit(string pathName)
        {
            if (InitialCheck(pathName, false))
            {
                DataTable dt = new DataTable();
                OleDbConnection conn = StartConnection(pathName);
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow row in dt.Rows)
                {
                    string sheetNameString = row["TABLE_NAME"].ToString();
                    if (DealWithInvalidSheetName(sheetNameString))
                    {
                        for (int i = 0; i < sheetNameSetString.Length; i++)
                        {
                            if (sheetNameString.Contains(sheetNameSetString[i]))
                            {
                                fillTable(sheetNameString, i, conn);
                            }
                        }
                    }
                }
                TableDataFormat();
                GetDateOrderTable();
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 获取Access文件表名，返回List
        /// </summary>
        /// <returns></returns>
        public static List<string> GetAccessTableName(string pathAccess)
        {
            List<string> tableName = new List<string>();
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathAccess);
            conn.Open();
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow row in dt.Rows)
            {
                string sheetNameString = row["table_name"].ToString();
                if (!sheetNameString.Contains("MSys")) tableName.Add(sheetNameString);
            }
            conn.Close();
            conn.Dispose();
            return tableName;
        }
        /// <summary>
        /// 获取Excel文件表名,返回List
        /// </summary>
        /// <param name="pathName">路径名</param>
        /// <returns></returns>
        public static List<string> GetExcelTableName(string pathName)
        {
            List<string> tableName = new List<string>();
            OleDbConnection conn = StartConnection(pathName);
            conn.Open();
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            foreach (DataRow row in dt.Rows)
            {
                string sheetNameString = row["table_name"].ToString();
                //过滤无效SheetName
                if (DealWithInvalidSheetName(sheetNameString))
                {
                    sheetNameString = sheetNameString.Replace("'", "");
                    sheetNameString = sheetNameString.Substring(0, sheetNameString.Length - 1);
                    tableName.Add(sheetNameString);
                }
            }
            conn.Close();
            conn.Dispose();
            return tableName;
        }
        /// <summary>
        /// 根据指定的Access表名，返回DataTable
        /// </summary>
        /// <param name="pathName"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable DBAccessToDataTable(string pathName, string sheetName)
        {
            DataTable table = new DataTable();
            if (IsTableNameExist(pathName, sheetName, true))
            {
                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathName);
                conn.Open();
                OleDbCommand cmd = new OleDbCommand("select * from [" + sheetName + "]", conn);
                OleDbDataAdapter apt = new OleDbDataAdapter(cmd);
                apt.Fill(table);
                table.TableName = sheetName;
                DeleteNullRow(ref table);
                DeleteNullColumn(ref table);
                DeleteColumnSpace(ref table);
                conn.Close();
                conn.Dispose();
            }
            else MessageBox.Show("表名不存在于该表中");
            return table;
        }
        /// <summary>
        /// 根据指定的Excel页表,返回DataTable
        /// </summary>
        /// <param name="pathName">路径名</param>
        /// <param name="sheetName">页表名</param>
        /// <returns></returns>
        public static DataTable DBExcelToDataTable(string pathName, string sheetName)
        {
            DataTable dt = new DataTable();
            if (IsTableNameExist(pathName, sheetName, false))
            {
                OleDbConnection conn = StartConnection(pathName);
                conn.Open();
                OleDbCommand cmd = new OleDbCommand("select * from [" + sheetName + "$]", conn);
                OleDbDataAdapter apt = new OleDbDataAdapter(cmd);
                try
                {
                    apt.Fill(dt);
                }
                catch (Exception e)
                {
                    MessageBox.Show("该Excel文件中未找到指定工作表名,");
                }
                dt.TableName = sheetName;
                DeleteNullRow(ref dt);
                DeleteNullColumn(ref dt);
                DeleteColumnSpace(ref dt);
                conn.Close();
                conn.Dispose();
            }
            else
            {
                MessageBox.Show("表名不存在于该表中");
            }
            return dt;
        }
        /// <summary>
        /// 通过Excel创建Access, 没有Access文件路径则创建在默认位置，没有传入创建的表名则创建全部表
        /// </summary>
        /// <param name="pathExcel">Excel文件路径</param>
        /// <param name="pathAccess">Access文件路径</param>
        public static void ExcelToAccessAuto(string pathExcel, string pathAccess = null, List<string> tableAccess = null)
        {
            List<string> tableName = GetExcelTableName(pathExcel);            
            //Access路径为空，则默认位置创建，默认位置为当前程序运行目录
            if (pathAccess == null)
            {
                pathAccess = CreateAccessFile();
            }
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathAccess);
            conn.Open();
            //如果没有传入需要创建的表名，则创建全部
            if (tableAccess == null)
            {
                if (DuplicateColumnNameDetection(pathExcel, tableName))
                {
                    for (int i = 0; i < tableName.Count; i++)
                    {                        
                        string sqlInsert = string.Format("select * into [{0}] from [{1}] in '{2}'[Excel 12.0;HDR=YES;IMEX=1]"
                                             , tableName[i], tableName[i] + "$", pathExcel);
                        OleDbCommand cmd = new OleDbCommand(sqlInsert, conn);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            //传入表名若表名正确则根据给定表名创建
            else
            {
                if (IsTableNameExist(pathExcel, tableAccess, false))
                {
                    if (DuplicateColumnNameDetection(pathExcel, tableAccess))
                    {
                        for (int i = 0; i < tableAccess.Count; i++)
                        {
                            string sqlInsert = string.Format("select * into [{0}] from [{1}] in '{2}'[Excel 12.0;HDR=YES;IMEX=1]"
                                             , tableAccess[i], tableAccess[i] + "$", pathExcel);
                            OleDbCommand cmd = new OleDbCommand(sqlInsert, conn);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                else MessageBox.Show("表名传入错误，与原文件表名不符");
            }
        }
        /// <summary>
        /// 导出至Access数据库
        /// </summary>
        /// <param name="accessPath"></param>
        /// <param name="tableName"></param>
        public static void ExportToAccessFile(string accessPath, List<string> tableName)
        {
            //string[] tableName = {"2-2 日产排序", "3-单井汇总", "4-日产汇总", "6-分类统计", "7-相关性" };
            if (dataTables[2].Rows.Count == 0) GetDateOrderTable();
            if (SingleWellSummary.singleWellSumTable.Rows.Count == 0) SingleWellSummary.SingleWellSum();
            if (DailyProductionSummary.dailyProduction.Rows.Count == 0) DailyProductionSummary.DailyProductionSum();
            //if (ClassificationStatistics.statisticsTable.Rows.Count == 0) ClassificationStatistics.Statistics();
            //if (Correlation.CorrelationTable.Rows.Count == 0) Correlation.GetCorrelationTable();
            for(int i=0; i<tableName.Count; i++)
            {
                if (tableName[i].Equals("1-静态")) ExportToAccess(dataTables[0], accessPath);
                if (tableName[i].Equals("2-1 日产")) ExportToAccess(dataTables[1], accessPath);
                if (tableName[i].Equals("2-2 日产排序")) ExportToAccess(dataTables[2], accessPath);
                if (tableName[i].Equals("3-单井汇总")) ExportToAccess(SingleWellSummary.singleWellSumTable, accessPath);
                if (tableName[i].Equals("4-日产汇总")) ExportToAccess(DailyProductionSummary.dailyProduction, accessPath);
                //if (tableName[i].Equals("6-分类统计")) ExportToAccess(ClassificationStatistics.statisticsTable, accessPath);
                //if (tableName[i].Equals("7-相关性")) ExportToAccess(Correlation.CorrelationTable, accessPath);
            }            
        }
        /// <summary>
        /// 根据dataTables[0]获取井名，返回List
        /// </summary>
        /// <returns></returns>
        public static List<string> GetWellName()
        {
            List<string> wellNameList = new List<string>();
            if (dataTables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dataTables[0].Rows.Count; i++)
                {
                    string wellName = dataTables[0].Rows[i]["井号"].ToString();
                    wellNameList.Add(wellName);
                }
            }
            return wellNameList;
        }
        /// <summary>
        /// 根据dataTables[2]获取年份，返回List
        /// </summary>
        /// <returns></returns>
        public static List<string> GetDateAsYearUnit()
        {
            List<string> dateList = new List<string>();
            int endRow = dataTables[2].Rows.Count - 1;
            DateTime startDate = Convert.ToDateTime(dataTables[2].Rows[0]["日期"]);
            DateTime endDate = Convert.ToDateTime(dataTables[2].Rows[endRow]["日期"]);
            for (int i = startDate.Year; i <= endDate.Year; i++)
            {
                dateList.Add(string.Format("{0}/1/1", i));
            }
            return dateList;
        }
        /// <summary>
        /// 对变量中的表名进行更改设置
        /// </summary>
        /// <param name="newSheetName"></param>
        public static void WriteSheetName(string[] newSheetName)
        {
            sheetNameSetString = newSheetName;
            return;
        }
        #endregion
        #region 私有函数
        /// <summary>
        /// 初始化检测有无对应表
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="mode">true为access，false为excel</param>
        /// <returns></returns>
        private static bool InitialCheck(string path, bool mode)
        {
            List<string> tableNameNeed = new List<string>();
            for (int i = 0; i < sheetNameSetString.Length; i++)
            {
                tableNameNeed.Add(sheetNameSetString[i]);
            }
            if (IsTableNameExist(path, tableNameNeed, mode))
                return true;
            else
            {
                string tip = "所需表";
                for (int i = 0; i < tableNameNeed.Count; i++)
                {
                    tip += string.Format(" {0} ", tableNameNeed[i]);
                }
                tip += "在文件中找不到";
                MessageBox.Show(tip);
            }
            return false;
        }
        /// <summary>
        /// 重复列名检测
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="tableNameList">检测表名</param>
        /// <returns></returns>
        private static bool DuplicateColumnNameDetection(string path, in List<string> tableNameList)
        {
            List<List<string>> columnName = GetExcelSheetColumnName(path, tableNameList);
            List<string> result = new List<string>();
            for(int i=0; i<columnName.Count; i++)
            {
                for(int j=1; j<columnName[i].Count; j++)
                {
                    //columnName[i].Sort();
                    if(columnName[i][j].Contains(columnName[i][j-1]))
                    {
                        result.Add(tableNameList[i]);
                        break;
                    }
                }
            }
            if (result.Count == 0) return true;
            else
            {
                string tip = string.Format("表");
                for(int i=0; i<result.Count; i++)
                {
                    tip += string.Format(" {0} ", result[i]);
                }
                tip += "有重复的列名，导致无法转换";
                MessageBox.Show(tip);
            }
            return false;
        }
        /// <summary>
        /// 获取Excel文件每个页表的列名(列名以字典序排列）
        /// </summary>
        /// <param name="pathName">Excel文件路径</param>
        /// <param name="tableName">需要检测的表名</param>
        /// <returns></returns>
        private static List<List<string>> GetExcelSheetColumnName(string pathName, List<string> tableName = null)
        {
            List<List<string>> columnName = new List<List<string>>();
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';";
            OleDbConnection conn = new OleDbConnection(connectionString);
            conn.Open();
            //没有需要检测的表名，则获取全部
            if (tableName == null)
            {
                DataTable dt = new DataTable();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow row in dt.Rows)
                {
                    string sheetNameString = row["TABLE_NAME"].ToString();
                    if (DealWithInvalidSheetName(sheetNameString))
                    {
                        List<string> colName = new List<string>();
                        DataTable sheetTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, sheetNameString, null });
                        foreach (DataRow rows in sheetTable.Rows)
                        {
                            colName.Add(rows["column_name"].ToString());
                            //colName.Add(rows["data_type"].ToString());
                        }
                        columnName.Add(colName);
                    }
                }
            }
            else
            {
                for(int i=0; i<tableName.Count; i++)
                {
                    List<string> colName = new List<string>();
                    string sheetName = "'"+tableName[i] + "$'";
                    DataTable sheetTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, sheetName, null });
                    foreach (DataRow rows in sheetTable.Rows)
                    {
                        colName.Add(rows["column_name"].ToString());
                        //colName.Add(rows["data_type"].ToString());
                    }
                    columnName.Add(colName);
                }
            }
            conn.Close();
            conn.Dispose();
            return columnName;
        }
        /// <summary>
        /// 建立数据库链接
        /// </summary>
        /// <param name="pathName">文件路径</param>
        private static OleDbConnection StartConnection(string pathName)
        {
            OleDbConnection conn;
            string connectionString = string.Empty;
            FileInfo file = new FileInfo(pathName);
            if (file.Exists)
            {
                string extension = file.Extension;
                connectionString = ConnStringSet(extension, pathName);
                conn = new OleDbConnection(connectionString);
                return conn;
            }
            else
                throw new Exception("文件不存在");
        }
        /// <summary>
        /// Excel方式OleDb链接字符串设置
        /// </summary>
        /// <param name="extension">扩展名</param>
        /// <param name="pathName">文件路径</param>
        /// <returns></returns>
        private static string ConnStringSet(string extension, string pathName)
        {
            string connectionString = string.Empty;
            switch (extension)
            {
                case ".xls":
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;'";
                    break;
                case ".xlsm":
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;'";
                    break;
                case ".xlsx":
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;'";
                    break;
                default:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;'";
                    break;
            }
            return connectionString;
        }
        /// <summary>
        /// OleDb方式读进来的同一列表会有多个表名，筛选需要的列表名
        /// </summary>
        /// <param name="sheetName">列表名</param>
        /// <returns></returns>
        private static bool DealWithInvalidSheetName(string sheetName)
        {
            if (sheetName.Contains("$") && sheetName.Replace("'", "").EndsWith("$"))
                return true;
            else return false;
        }
        /// <summary>
        /// 删除空列，依据列名为默认（如F10）
        /// </summary>
        /// <param name="dt">DataTable的引用</param>
        private static void DeleteNullColumn(ref DataTable dt)
        {
            for (int i = dt.Columns.Count - 1; i >= 0; i--)
            {
                if (dt.Columns[i].ColumnName.Contains("F"))
                    dt.Columns.Remove(dt.Columns[i].ColumnName);
            }
        }
        /// <summary>
        /// 删除空行，依据为该行无数据
        /// </summary>
        /// <param name="dt">DataTable的引用</param>
        private static void DeleteNullRow(ref DataTable dt)
        {
            for (int i = dt.Rows.Count - 1; i >= 0; i--)
            {
                bool flag = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!dt.Rows[i][j].Equals(DBNull.Value))
                    {
                        flag = false;
                        break;
                    }
                }
                if (flag) dt.Rows.RemoveAt(i);
            }
        }
        /// <summary>
        /// 删除列名中的空格
        /// </summary>
        /// <param name="dt">表的引用</param>
        private static void DeleteColumnSpace(ref DataTable dt)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Trim();
            }
        }
        /// <summary>
        /// 对dataTables的数据进行格式上的处理
        /// </summary>
        private static void TableDataFormat()
        {
            for (int i = 0; i < dataTables[0].Rows.Count; i++)
            {
                dataTables[0].Rows[i][0] = Convert.ToInt32(dataTables[0].Rows[i][0]);
            }
            for (int i = 0; i < dataTables[1].Rows.Count; i++)
            {
                dataTables[1].Rows[i][1] = Convert.ToInt32(dataTables[1].Rows[i][1]);
            }
        }
        /// <summary>
        /// 通过对dataTables[1]日期排序得到dataTables[2]
        /// </summary>
        private static void GetDateOrderTable()
        {
            var table = from p in dataTables[1].AsEnumerable()
                        orderby p.Field<DateTime>("日期")
                        select p;
            int index = sheetNameSetString.Length - 1;
            dataTables[index] = table.CopyToDataTable<DataRow>();
            dataTables[index].TableName = "2-2 日产排序";
        }
        /// <summary>
        /// 根据页表名填充dataTables
        /// </summary>
        /// <param name="sheetNameString">页表名</param>
        /// <param name="conn">数据库链接</param>
        private static void fillTable(string sheetNameString, int index, OleDbConnection conn)
        {
            string commandString = "select * from [" + sheetNameString + "]";
            OleDbCommand cmd = new OleDbCommand(commandString, conn);
            OleDbDataAdapter apt = new OleDbDataAdapter(cmd);
            dataTables[index] = new DataTable();
            apt.Fill(dataTables[index]);
            dataTables[index].TableName = sheetNameSetString[index];
            DeleteNullColumn(ref dataTables[index]);
            DeleteColumnSpace(ref dataTables[index]);
            DeleteNullRow(ref dataTables[index]);
        }
        /// <summary>
        /// 创建一个Access文件
        /// </summary>
        /// <param name="pathAccess">Access文件路径</param>
        private static string CreateAccessFile()
        {
            string pathAccess = AppDomain.CurrentDomain.BaseDirectory + "\\CreateData.accdb";
            Catalog catalog = new Catalog();
            if (File.Exists(pathAccess))
            {
                File.Delete(pathAccess);
            }
            catalog.Create("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathAccess);
            return pathAccess;
        }
        /// <summary>
        /// 由datatable至Access数据库
        /// </summary>
        /// <param name="table">源数据表</param>
        /// <param name="accessPath">Access文件路径</param>
        private static void ExportToAccess(in DataTable table, string accessPath)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + accessPath);
            conn.Open();
            OleDbCommand cmd;
            List<string> oldTableName = GetAccessTableName(accessPath);
            if(oldTableName.Contains(table.TableName))
            {
                cmd = new OleDbCommand(string.Format("drop table [{0}]", table.TableName), conn);
                cmd.ExecuteNonQuery();
            }
            SQLCreateTable(table, conn);
            //SQLInsertData(table, conn);
            DataTable tmpTable = new DataTable();
            string sql = string.Format("select * from [{0}]", table.TableName);
            OleDbDataAdapter apt = new OleDbDataAdapter(sql, conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(apt);
            builder.QuotePrefix = "[";
            builder.QuoteSuffix = "]";
            apt.Fill(tmpTable);
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow dataRow = tmpTable.NewRow();
                dataRow.ItemArray = table.Rows[i].ItemArray;
                tmpTable.Rows.Add(dataRow);
            }
            apt.InsertCommand = builder.GetInsertCommand();
            apt.Update(tmpTable);
            conn.Close();
            conn.Dispose();
        }
        /// <summary>
        /// 根据table建表
        /// </summary>
        /// <param name="table">原数据表</param>
        /// <param name="conn">Oledb链接</param>
        private static void SQLCreateTable(in DataTable table, OleDbConnection conn)
        {
            string sql = string.Format("create table [{0}])", table.TableName);            
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();
            for(int i=0; i<table.Columns.Count; i++)
            {
                sql = string.Format("alter table [{0}] add [{1}] varchar",
                    table.TableName, table.Columns[i].ColumnName);
                cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }            
        }
        /// <summary>
        /// 将数据插入到表中
        /// </summary>
        /// <param name="table">存放数据的表</param>
        /// <param name="conn">OleDb链接</param>
        private static void SQLInsertData(in DataTable table, OleDbConnection conn)
        {
            for(int i=0; i<table.Rows.Count; i++)
            {
                string sql = string.Format("insert into [{0}] values (", table.TableName);
                for(int j=0; j<table.Columns.Count; j++)
                {
                    string value;
                    if (table.Rows[i][j].Equals(DBNull.Value))
                        value = string.Empty;
                    else
                        value = table.Rows[i][j].ToString();
                    sql += string.Format("'{0}'", value);
                    if (j != table.Columns.Count - 1)
                        sql += ",";
                }
                sql += ")";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 判断表名是否在Access或Excel文件中，
        /// </summary>
        /// <param name="pathName">文件路径</param>
        /// <param name="tableNameCompare">需要比较的表名</param>
        /// <param name="mode">true为access，false为excel</param>
        /// <returns></returns>
        private static bool IsTableNameExist(string pathName, List<string> tableNameCompare, bool mode)
        {
            for (int i = 0; i < tableNameCompare.Count; i++)
            {
                string sheetName = tableNameCompare[i];
                if (!IsTableNameExist(pathName, sheetName, mode))
                    return false;
            }
            return true;
        }
        /// <summary>
        /// 判断表名是否存在于该表中
        /// </summary>
        /// <param name="pathName">文件路径</param>
        /// <param name="sheetName">表名</param>
        /// <param name="mode">true为Access文件，false为Excel文件</param>
        /// <returns></returns>        
        private static bool IsTableNameExist(string pathName, string sheetName, bool mode)
        {
            List<string> tableName = new List<string>();
            if (mode) tableName = GetAccessTableName(pathName);
            else tableName = GetExcelTableName(pathName);
            for (int i = 0; i < tableName.Count; i++)
            {
                if (sheetName.Equals(tableName[i]))
                    return true;
            }
            return false;
        }
        #endregion
    }
}
